package com.lyouu.dataminer.cruisetour;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.apache.log4j.Logger;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.db.DbLyouuParams;
import com.lyouu.dataminer.factory.DaoFactory;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.CategorizerUtil;
import com.lyouu.dataminer.util.Constants;

public class CruiseTour {
	private static Logger logger = Logger.getLogger(CruiseTour.class);

	public static void insertCruiseTour(String booking, String owner,
			String name, String imgUrl, String content, String departCity,
			String routine, String currency, String price, String provider,
			String childPrice, String groupPrice, String departCountry,
			ArrayList<String> departDateList, ArrayList<String> endDateList,
			String highlights, String lineArea) throws SQLException {
		int sid = DaoFactory.getMdSubjectCruisetourDao().getSidByOrigPageUrl(
				booking);
		// logger.info(sid + "\n" + name + "\n" + booking);
		if (sid == Constants.CRUISE_TOUR_DEFAULT_SID) {
			sid = DbLyouuParams.getParams().getNextSid();
			int nextAlbumId = DbLyouuParams.getParams().getNextAlbumId();
			int nextPicId = DbLyouuParams.getParams().getNextPicId();
			DaoFactory.getMdSubjectDao().insert(sid,
					Constants.CRUISE_TOUR_CATEGORY_CATID, name, owner, imgUrl);
			int uid = DaoFactory.getMdMembersDao().getUidByUsername(owner);
			DaoFactory.getMdMysubjectDao().insert(uid, sid, Constants.CRUISE_TOUR_MODEL_ID);
			if (!imgUrl.equals(Constants.NO_THUMB)) {
				DaoFactory.getMdAlbumDao().insert(nextAlbumId, sid, name,
						imgUrl);
				DaoFactory.getMdPicturesDao().insert(nextPicId, nextAlbumId,
						sid, sid, owner, imgUrl);
			}
			String travelDate = "";
			if (!departDateList.isEmpty()) {
				int size = departDateList.size();
				for (int i = 0; i < size - 1; i++) {
					travelDate += departDateList.get(i) + "-"
							+ endDateList.get(i) + " | ";
				}
				travelDate += departDateList.get(size - 1) + "-"
						+ endDateList.get(size - 1);
			}
			String tourAreaAttidStr = CategorizerUtil.selectTourArea(lineArea,
					name);
			DaoFactory.getMdSubjectCruisetourDao().insert(sid, content,
					departCity, routine, currency, price, provider, booking,
					childPrice, groupPrice, tourAreaAttidStr, travelDate,
					departCountry, highlights);
			// update table 'md_subjectatt'
			ArrayList<Integer> tourAreaAttidList = new ArrayList<Integer>();
			String[] tourAreaAttidAry = tourAreaAttidStr.split(",");
			for (int i = 0; i < tourAreaAttidAry.length; i++)
				tourAreaAttidList.add(Integer.valueOf(tourAreaAttidAry[i]));
			DaoFactory.getMdSubjectattDao().insert(sid,
					Constants.CRUISE_TOUR_CATEGORY_ATTID, tourAreaAttidList,
					departCountry);
		}
	}

	public static void insertCruisetourSchedule(String owner, String tourName,
			List<String> groupIdList, List<String> departDateList,
			List<String> endDateList, String currency, List<String> priceList,
			List<String> childPriceList, List<String> groupPriceList,
			List<String> statusList, List<String> bookingList, String pageUrl)
			throws SQLException {
		DaoFactory.getCruisetourScheduleDao().insert(owner, tourName,
				groupIdList, departDateList, endDateList, currency, priceList,
				childPriceList, groupPriceList, statusList, bookingList,
				pageUrl);
	}

	public static void deleteSubjects(String owner) throws SQLException {
		int minSid = DbLyouuParams.getParams().getMinSidByOwner(owner,
				Constants.CRUISE_TOUR_CATEGORY_CATID);
		int maxSid = DbLyouuParams.getParams().getMaxSidByOwner(owner,
				Constants.CRUISE_TOUR_CATEGORY_CATID);

		minSid--;
		maxSid++;

		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "DELETE FROM ly_cruisetour_schedule WHERE sid>? AND sid<?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, minSid);
		stmt.setInt(2, maxSid);
		stmt.executeUpdate();

		if (Constants.CRUISE_TOUR_DELETE_OLD_SUBJECTS) {
			sql = "DELETE FROM md_subject WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();

			sql = "DELETE FROM md_subjectatt WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();

			sql = "DELETE FROM md_album WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();

			sql = "DELETE FROM md_pictures WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();

			sql = "DELETE FROM md_subject_cruisetour WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();

			sql = "DELETE FROM md_mysubject WHERE sid>? AND sid<?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, minSid);
			stmt.setInt(2, maxSid);
			stmt.executeUpdate();
		}
		conn.close();
	}
}
